#!/use/bin/python
# coding=utf-8
# 主动服务 开机率 analysis_startup_rate
import datetime
import pymysql

from dbutils.pooled_db import PooledDB



# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


def deleteLog(key):
    try:
        conn, cur = getDataConnection()
        # 修改 analysis_subscribe_number 数据
        selectSql = f"""select t.id from log_common_service t where t.`key` = '{key}' limit 500000 """
        cur.execute(selectSql)
        selectResult = cur.fetchall()
        deleteSql = "delete from log_common_service where 1=1"
        delNum = 1
        dateStr = ""
        for result in selectResult:
            id = result[0]
            dateStr = f"{dateStr} {str(id)} ,"
            delNum  = delNum+1
            if delNum>10000:
                deleteSql2 = f"{deleteSql} and id in ( {dateStr[:-1]} )"
                #print(deleteSql2)
                cur.execute(deleteSql2)
                conn.commit()
                delNum = 1
                dateStr= ""
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def deleteAskShow():
    try:
        conn, cur = getDataConnection()
        # 修改 analysis_subscribe_number 数据
        selectSql = f"""select t.id from log_common_service t where t.`key` = 'askShow' and JSON_EXTRACT(t.msg,'$.result') =0 limit 1000000 """
        cur.execute(selectSql)
        selectResult = cur.fetchall()
        deleteSql = "delete from log_common_service where 1=1"
        delNum = 1
        dateStr = ""
        for result in selectResult:
            id = result[0]
            dateStr = f"{dateStr} {str(id)} ,"
            delNum  = delNum+1
            if delNum>10000:
                deleteSql2 = f"{deleteSql} and id in ( {dateStr[:-1]} )"
                #print(deleteSql2)
                cur.execute(deleteSql2)
                delNum = 1
                dateStr= ""
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


def deleteLogCommonService():
    try:
        conn, cur = getDataConnection()
        # 修改 analysis_subscribe_number 数据
        selectSql = f"""select t.id from log_common_service t where t.id < 141566777 limit 1000000 """
        cur.execute(selectSql)
        selectResult = cur.fetchall()
        deleteSql = "delete from log_common_service where 1=1"
        delNum = 1
        dateStr = ""
        for result in selectResult:
            id = result[0]
            dateStr = f"{dateStr} {str(id)} ,"
            delNum  = delNum+1
            if delNum>10000:
                deleteSql2 = f"{deleteSql} and id in ( {dateStr[:-1]} )"
                #print(deleteSql2)
                cur.execute(deleteSql2)
                conn.commit()
                delNum = 1
                dateStr= ""
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #deleteLog('musicPlayerError');
    #deleteLog('askPreStart');
    #deleteAskShow()
    deleteLogCommonService()